package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.CellFormat;
import jxl.Workbook;
import jxl.biff.FontRecord;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.apache.commons.net.ntp.TimeStamp;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.jboss.util.propertyeditor.BigDecimalEditor;
import org.joda.time.DateTime;

import com.lowagie.text.Font;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class FI_Form3Direct extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private String startDate = null;
	private String endDate = null;
	private String startDateMinusYear = null;
	private String endDateMinusYear = null;
	private String getSQlresult = null;
	private Timestamp tsEndDate = null;
	private Timestamp tsStartDate = null;
	private Timestamp tsEndDateMinusYear = null;
	private Timestamp tsStartDateMinusYear = null;	
	private boolean Thousand = false;

	private static CLogger s_log = CLogger.getCLogger(BalanceSheets.class);
	/** */
	private String path = "";

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			 else if (name.equals("startDate"))
				tsStartDate = (Timestamp) para[i].getParameter();
			else if (name.equals("endDate"))
				tsEndDate = (Timestamp) para[i].getParameter();
			 else if (name.equals("Thousand"))
			 Thousand = ((String) para[i].getParameter()).equals("Y");
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		Date lEndDate = new Date(tsEndDate.getTime());
		SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");
		int year = lEndDate.getYear() + 1900;
		endDate = format.format(lEndDate);
 
		endDateMinusYear = endDate.substring(0, 6) + String.valueOf(year - 1);

		 lEndDate = new Date(tsStartDate.getTime());
		 year = lEndDate.getYear() + 1900;
		 startDate = format.format(lEndDate);
		
		 startDateMinusYear = startDate.substring(0, 6) + String.valueOf(year
		 - 1);

//		startDate = "01.01." + String.valueOf(year);
//		startDateMinusYear = "01.01." + String.valueOf(year - 1);
		// endDateMinusYear = endDate.substring(0, 6)
		// + String.valueOf(year - 1);

		// ---------- get Timestamp dates ---------------{
//		Date d = null;
		Date d2 = null;
		Date d3 = null;
		try {
//			d = format.parse(startDate);
			d2 = format.parse(startDateMinusYear);
			d3 = format.parse(endDateMinusYear);			
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		d.parse(endDate);
//		tsStartDate = new Timestamp(d.getTime());
		tsStartDateMinusYear = new Timestamp(d2.getTime());
		tsEndDateMinusYear = new Timestamp(d3.getTime());		
		//}
		
		
		path = Util.localFilePath;
		// / here another code)
	}

	@Override
	protected String doIt() throws Exception {
		// Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName = "FI_Form3Direct_tmpl.xls";
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate") + " " + templateName);

		File templateCopy = new File(Util.localFilePath + "FI_Form3Direct.xls");
		File temporaryFile = entry.getFile(Util.localFilePath + templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
			return "Error tableBook.";
		}

		String org_name;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sql = new StringBuilder(
				"select name from ad_org where ad_org_id = 1000000");
		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				org_name = rs.getString(1);
				WritableFont arial10ptBold = new WritableFont(
						WritableFont.TIMES, 9, WritableFont.NO_BOLD);
				WritableCellFormat captionStyle = new WritableCellFormat(
						arial10ptBold);
				label = new Label(0, 5,
						("Наименование организации: " + org_name), captionStyle);
				sheet.addCell(label);

			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_Form3Direct", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		// ExcelCell cellStart = Utils.getCellStart(tableBook,">>");
		// Style for value column
		WritableFont arial10ptBold = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.NO_BOLD);
		WritableCellFormat valuestyle = null;
		if(Thousand)
			valuestyle = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_INTEGER);
		else
			valuestyle = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_FLOAT); 		
		// WritableCellFormat valuestyle = new WritableCellFormat();
		valuestyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setAlignment(Alignment.RIGHT);
		valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
//		valuestyle.setBackground(Colour.getInternalColour(44));
		valuestyle.setWrap(true);

		WritableFont arial10ptBold22 = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.BOLD);
		WritableCellFormat valuestyle22 = null;
		if(Thousand)
			valuestyle22 = new WritableCellFormat(arial10ptBold22,NumberFormats.THOUSANDS_INTEGER);
		else
			valuestyle22 = new WritableCellFormat(arial10ptBold22,NumberFormats.THOUSANDS_FLOAT); 		
		// WritableCellFormat valuestyle = new WritableCellFormat();
		valuestyle22.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle22.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle22.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle22.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle22.setAlignment(Alignment.RIGHT);
		valuestyle22.setVerticalAlignment(VerticalAlignment.CENTRE);
		valuestyle22.setBackground(Colour.getInternalColour(31));
		valuestyle22.setWrap(true);
		
		
		WritableFont arial10ptNoBold = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.NO_BOLD);
		WritableCellFormat  valuestyleNoBold = null;
		if(Thousand)
			valuestyleNoBold = new WritableCellFormat(arial10ptNoBold,NumberFormats.THOUSANDS_INTEGER);
		else
			valuestyleNoBold = new WritableCellFormat(arial10ptNoBold,NumberFormats.THOUSANDS_FLOAT);		

		valuestyleNoBold.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setAlignment(Alignment.RIGHT);
		valuestyleNoBold.setVerticalAlignment(VerticalAlignment.CENTRE);
		valuestyleNoBold.setWrap(true);

		WritableFont arial11ptBold = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.BOLD);
		WritableCellFormat  valuestyleBold = null;
		if(Thousand)
			valuestyleBold = new WritableCellFormat(arial11ptBold,NumberFormats.THOUSANDS_INTEGER);
		else
			valuestyleBold = new WritableCellFormat(arial11ptBold,NumberFormats.THOUSANDS_FLOAT);		

		valuestyleBold.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleBold.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleBold.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleBold.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleBold.setAlignment(Alignment.RIGHT);
		valuestyleBold.setVerticalAlignment(VerticalAlignment.CENTRE);
		valuestyleBold.setBackground(Colour.getInternalColour(31));		
		valuestyleBold.setWrap(true);		
//		
//		for(int i=1; i<100;i++){
//			WritableCellFormat valuestyle22 = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_INTEGER);;
//			valuestyle22.setBackground(Colour.getInternalColour(i));
//			label = new Label(10, i, Integer.valueOf(i).toString(), valuestyle22);
//			sheet.addCell(label);
//		}
		
		if(!Thousand){
			WritableCellFormat valuestyle3 = new WritableCellFormat();
			label = new Label(2, 9, "", valuestyle3);
			sheet.addCell(label);
			label = new Label(3, 9, "", valuestyle3);
			sheet.addCell(label);

		}
		
		String caption = " за период, заканчивающийся "
				+ endDate.substring(0, 2)
				+ " "
				+ org.utils.DataUtils.getMonthNameRU(
						new Date(tsEndDate.getTime())).toLowerCase() + " "
				+ endDate.substring(6, 10) + " года.";

		WritableFont arial10ptBold2 = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.BOLD);
		WritableCellFormat captionStyle = new WritableCellFormat(arial10ptBold2);
		captionStyle.setAlignment(Alignment.CENTRE);
		captionStyle.setVerticalAlignment(VerticalAlignment.CENTRE);

		label = new Label(0, 8, caption, captionStyle);
		sheet.addCell(label);

		if (getSQlresult != null)
			return Msg.translate(m_ctx, "Ошибка запроса.");				
		
		// output datas ----------------------------------{
		//current year
		number = new Number(2, 14, getData(tsStartDate, tsEndDate, "011").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 15, getData(tsStartDate, tsEndDate, "012").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 16, getData(tsStartDate, tsEndDate, "013").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 17, getData(tsStartDate, tsEndDate, "014").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 18, getData(tsStartDate, tsEndDate, "015").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 19, getData(tsStartDate, tsEndDate, "016").doubleValue(), valuestyle);
		sheet.addCell(number);
		
		number = new Number(2, 22, getData(tsStartDate, tsEndDate, "021").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 23, getData(tsStartDate, tsEndDate, "022").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 24, getData(tsStartDate, tsEndDate, "023").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 25, getData(tsStartDate, tsEndDate, "024").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 26, getData(tsStartDate, tsEndDate, "025").doubleValue(), valuestyle);
		sheet.addCell(number);

		
//		double v062 = getData(tsStartDate, tsEndDate, "062").doubleValue();
//		double v027 = getData(tsStartDate, tsEndDate, "026").doubleValue();
//		double vDiff = v062 / 112 * 100;
//		v027 += v062 - vDiff;
//		v062 = vDiff;
//		
//		double v061 = getData(tsStartDate, tsEndDate, "061").doubleValue();
//		double v026 = getData(tsStartDate, tsEndDate, "026").doubleValue();
//		double vDiffx = v061 / 112 * 100;
//		v027 += v061 - vDiff;
//		v061 = vDiffx;
		double v061 = getData(tsStartDate, tsEndDate, "061").doubleValue();
		double v062 = getData(tsStartDate, tsEndDate, "062").doubleValue();
		double v026 = getData(tsStartDate, tsEndDate, "026").doubleValue();
		double vDiff = v061 / 112 * 100;
		  v026 += v061 - vDiff;
		  v061 = vDiff;
		  
		  vDiff = v062 / 112 * 100;
		  v026 += v062 - vDiff;
		  v062 = vDiff;

		
		
		number = new Number(2, 27, v026, valuestyle);
		sheet.addCell(number);
		number = new Number(2, 28, getData(tsStartDate, tsEndDate, "027").doubleValue(), valuestyle);
		sheet.addCell(number);
		
		number = new Number(2, 33, getData(tsStartDate, tsEndDate, "041").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 34, getData(tsStartDate, tsEndDate, "042").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 35, getData(tsStartDate, tsEndDate, "043").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 36, getData(tsStartDate, tsEndDate, "044").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 37, getData(tsStartDate, tsEndDate, "045").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 38, getData(tsStartDate, tsEndDate, "046").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 39, getData(tsStartDate, tsEndDate, "047").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 40, getData(tsStartDate, tsEndDate, "048").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 41, getData(tsStartDate, tsEndDate, "049").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 42, getData(tsStartDate, tsEndDate, "050").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 43, getData(tsStartDate, tsEndDate, "051").doubleValue(), valuestyle);
		sheet.addCell(number);

		number = new Number(2, 46, v061, valuestyle);
		sheet.addCell(number);
		
		number = new Number(2, 47, v062, valuestyle);
		sheet.addCell(number);
		number = new Number(2, 48, getData(tsStartDate, tsEndDate, "063").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 49, getData(tsStartDate, tsEndDate, "064").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 50, getData(tsStartDate, tsEndDate, "065").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 51, getData(tsStartDate, tsEndDate, "066").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 52, getData(tsStartDate, tsEndDate, "067").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 53, getData(tsStartDate, tsEndDate, "068").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 54, getData(tsStartDate, tsEndDate, "069").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 55, getData(tsStartDate, tsEndDate, "070").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 56, getData(tsStartDate, tsEndDate, "071").doubleValue(), valuestyle);
		sheet.addCell(number);

		number = new Number(2, 61, getData(tsStartDate, tsEndDate, "091").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 62, getData(tsStartDate, tsEndDate, "092").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 63, getData(tsStartDate, tsEndDate, "093").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 64, getData(tsStartDate, tsEndDate, "094").doubleValue(), valuestyle);
		sheet.addCell(number);

		number = new Number(2, 67, getData(tsStartDate, tsEndDate, "101").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 68, getData(tsStartDate, tsEndDate, "102").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 69, getData(tsStartDate, tsEndDate, "103").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 70, getData(tsStartDate, tsEndDate, "104").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 71, getData(tsStartDate, tsEndDate, "105").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(2, 75, getDatabyAcct(tsStartDate, tsEndDate, "104%").doubleValue(), valuestyle22);
		sheet.addCell(number);		
		
		// set formulas -----------------------------------------------------------------------------
		Formula formula = null;
		
		formula = new Formula(2,12,"SUM(C15:C20)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);				

		formula = new Formula(2,20,"SUM(C23:C29)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);	

		formula = new Formula(2,31,"SUM(C34:C44)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);
		
		formula = new Formula(2,44,"SUM(C47:C57)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);

		formula = new Formula(2,57,"C32-C45");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);

		formula = new Formula(2,74,"C30+C58+C73");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);

		formula = new Formula(2,76,"C75+C76");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);		
		
		
		//prev year
		number = new Number(3, 14, getData(tsStartDateMinusYear, tsEndDateMinusYear, "011").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 15, getData(tsStartDateMinusYear, tsEndDateMinusYear, "012").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 16, getData(tsStartDateMinusYear, tsEndDateMinusYear, "013").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 17, getData(tsStartDateMinusYear, tsEndDateMinusYear, "014").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 18, getData(tsStartDateMinusYear, tsEndDateMinusYear, "015").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 19, getData(tsStartDateMinusYear, tsEndDateMinusYear, "016").doubleValue(), valuestyle);
		sheet.addCell(number);
		
		number = new Number(3, 22, getData(tsStartDateMinusYear, tsEndDateMinusYear, "021").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 23, getData(tsStartDateMinusYear, tsEndDateMinusYear, "022").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 24, getData(tsStartDateMinusYear, tsEndDateMinusYear, "023").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 25, getData(tsStartDateMinusYear, tsEndDateMinusYear, "024").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 26, getData(tsStartDateMinusYear, tsEndDateMinusYear, "025").doubleValue(), valuestyle);
		sheet.addCell(number);
		
		
		double v061p = getData(tsStartDateMinusYear, tsEndDateMinusYear, "061").doubleValue();
		double v062p = getData(tsStartDateMinusYear, tsEndDateMinusYear, "062").doubleValue();
		double v026p = getData(tsStartDateMinusYear, tsEndDateMinusYear, "026").doubleValue();
		double vDiffp = v061p / 112 * 100;
		  v026p += v061p - vDiffp;
		  v061p = vDiffp;
		  
		  vDiffp = v062p / 112 * 100;
		  v026p += v062p - vDiffp;
		  v062p = vDiffp;
		  
		number = new Number(3, 27, v026p, valuestyle);
		sheet.addCell(number);
		number = new Number(3, 28, getData(tsStartDateMinusYear, tsEndDateMinusYear, "027").doubleValue(), valuestyle);
		sheet.addCell(number);
		
		number = new Number(3, 33, getData(tsStartDateMinusYear, tsEndDateMinusYear, "041").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 34, getData(tsStartDateMinusYear, tsEndDateMinusYear, "042").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 35, getData(tsStartDateMinusYear, tsEndDateMinusYear, "043").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 36, getData(tsStartDateMinusYear, tsEndDateMinusYear, "044").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 37, getData(tsStartDateMinusYear, tsEndDateMinusYear, "045").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 38, getData(tsStartDateMinusYear, tsEndDateMinusYear, "046").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 39, getData(tsStartDateMinusYear, tsEndDateMinusYear, "047").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 40, getData(tsStartDateMinusYear, tsEndDateMinusYear, "048").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 41, getData(tsStartDateMinusYear, tsEndDateMinusYear, "049").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 42, getData(tsStartDateMinusYear, tsEndDateMinusYear, "050").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 43, getData(tsStartDateMinusYear, tsEndDateMinusYear, "051").doubleValue(), valuestyle);
		sheet.addCell(number);

		number = new Number(3, 46, getData(tsStartDateMinusYear, tsEndDateMinusYear, "061").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 47, getData(tsStartDateMinusYear, tsEndDateMinusYear, "062").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 48, getData(tsStartDateMinusYear, tsEndDateMinusYear, "063").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 49, getData(tsStartDateMinusYear, tsEndDateMinusYear, "064").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 50, getData(tsStartDateMinusYear, tsEndDateMinusYear, "065").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 51, getData(tsStartDateMinusYear, tsEndDateMinusYear, "066").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 52, getData(tsStartDateMinusYear, tsEndDateMinusYear, "067").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 53, getData(tsStartDateMinusYear, tsEndDateMinusYear, "068").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 54, getData(tsStartDateMinusYear, tsEndDateMinusYear, "069").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 55, getData(tsStartDateMinusYear, tsEndDateMinusYear, "070").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 56, getData(tsStartDateMinusYear, tsEndDateMinusYear, "071").doubleValue(), valuestyle);
		sheet.addCell(number);

		number = new Number(3, 61, v061p, valuestyle);
		sheet.addCell(number);
		number = new Number(3, 62, v062p, valuestyle);
		sheet.addCell(number);
		number = new Number(3, 63, getData(tsStartDateMinusYear, tsEndDateMinusYear, "093").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 64, getData(tsStartDateMinusYear, tsEndDateMinusYear, "094").doubleValue(), valuestyle);
		sheet.addCell(number);

		number = new Number(3, 67, getData(tsStartDateMinusYear, tsEndDateMinusYear, "101").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 68, getData(tsStartDateMinusYear, tsEndDateMinusYear, "102").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 69, getData(tsStartDateMinusYear, tsEndDateMinusYear, "103").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 70, getData(tsStartDateMinusYear, tsEndDateMinusYear, "104").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 71, getData(tsStartDateMinusYear, tsEndDateMinusYear, "105").doubleValue(), valuestyle);
		sheet.addCell(number);
		number = new Number(3, 75, getDatabyAcct(tsStartDateMinusYear,tsEndDateMinusYear,  "104%").doubleValue(), valuestyle22);
		sheet.addCell(number);		
		
		// set formulas -----------------------------------------------------------------------------
		formula = new Formula(3,12,"SUM(D15:D20)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);				

		formula = new Formula(3,20,"SUM(D23:D29)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);	

		formula = new Formula(3,31,"SUM(D34:D44)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);
		
		formula = new Formula(3,44,"SUM(D47:D57)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);

		formula = new Formula(3,57,"D32-D45");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);

		formula = new Formula(3,74,"D30+D58+D73");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);

		formula = new Formula(3,76,"D75+D76");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);		
		
		
		// output datas -----------------------------------------}
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();

		try {
			String fileName = Util.localFilePath + "FI_Form3Direct.xls";
			String command = "excel \"" + fileName + "\"";
			Runtime.getRuntime().exec("cmd /c start " + command);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return Msg.translate(m_ctx, "Success");
	}

	private BigDecimal getData(Timestamp d1, Timestamp d2, String fi_code) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BigDecimal result = new BigDecimal(0);

		StringBuilder sql = getSQl(d1, d2, fi_code);

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				result = rs.getBigDecimal(1);
			}

			if(result==null) result = new BigDecimal(0);
			result = (Thousand && result.compareTo(new BigDecimal(0)) != 0) ? (result =  result.divide(new BigDecimal(1000))) : result;

		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_Form3Direct", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return result;
	}

	private BigDecimal getDatabyAcct(Timestamp d, Timestamp d2, String acct) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BigDecimal result = new BigDecimal(0);

		StringBuilder sql = getSQlByAcct(d,d2, acct);

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				result = rs.getBigDecimal(1);
			}

			if(result==null) result = new BigDecimal(0);
			result = (Thousand && result.compareTo(new BigDecimal(0)) != 0) ? (result =  result.divide(new BigDecimal(1000))) : result;

		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_Form3Direct", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return result;
	}
	
	class line {
		String fi_code = null;
		BigDecimal value = null;
	}

//	private StringBuilder getSQl(Timestamp PstartDate, Timestamp PendDate, String fi_code) {
//		StringBuilder sql = new StringBuilder();
//		sql.append(
////				"select "
////				+ " sum(cp.payamt), f3.fi_code " 
////				+ " from c_payment cp "
////				+ " inner join fi_form3 f3 on f3.fi_form3_id = cp.fi_form3_id "
////				+ " where dateacct BETWEEN '" + PstartDate + "' and '"+ PendDate +"' "
////				+ " and fi_code = '" + fi_code + "' "
////				+ " group by f3.fi_code "
//				" select(	"
//				+ " 						(SELECT case when sum(fa.amtacctcr)  is null then 0 else sum(fa.amtacctcr)  end	"
//				+ " 							FROM adempiere.c_bankstatement h	"
//				+ " 							inner join adempiere.c_bankstatementline l on l.c_bankstatement_id = h.c_bankstatement_id	"
//				+ "								inner join fact_acct fa on fa.record_id = l.c_bankstatementline_id	"	
//				+ " 							inner join fi_form3 f3 on f3.fi_form3_id = l.fi_form3_id	"
//				+ " 							where h.statementdate between '" + PstartDate + "' and '"+ PendDate +"' 	"
//				+ " 							and f3.fi_code = '" + fi_code + "'	"
//				+ "								and fa.ad_table_id = 393	"
//				+ " 							and f3.fi_form3_id not in (select fi_form3_id from adempiere.fi_form3exclude where isactive = 'Y' )  "
//				+ " 						)	"
//				+ " 						+ 	"
//				+ " 						(select	" 
//				+ " 							case when sum(fa.amtacctcr) is null then 0 else sum(fa.amtacctcr) end		"
//				+ " 							from c_payment cp 		"
//				+ " 							inner join fi_form3 f3 on f3.fi_form3_id = cp.fi_form3_id	"	
//				+ "								inner join fact_acct fa on fa.record_id = cp.c_payment_id 	"
//				+ " 							where cp.dateacct between '" + PstartDate + "' and '"+ PendDate +"' 		"
//				+ " 							and f3.fi_code = '" + fi_code + "'	"
//				+ "								and fa.ad_table_id = 335	"
//				+ " 							and f3.fi_form3_id not in (select fi_form3_id from adempiere.fi_form3exclude where isactive = 'Y' )  "				
//				+ " 						)	"
//				+ " 						+	"
//				+ " 						(	"
//				+ " 						select case when sum(fa.amtacctcr) is null then 0 else sum(fa.amtacctcr) end	"
//				+ " 							from adempiere.gl_journal h	"
//				+ " 							inner join adempiere.gl_journalline l on l.gl_journal_id = h.gl_journal_id	"
//				+ "								inner join fact_acct fa on fa.record_id = l.gl_journalline_id	"
//				+ " 							inner join fi_form3 f3 on f3.fi_form3_id = l.fi_form3_id 	"
//				+ " 							where h.dateacct between '" + PstartDate + "' and '"+ PendDate +"'	"
//				+ " 							and f3.fi_code = '" + fi_code + "'	"
//				+ "								and fa.ad_table_id = 226	"				
//				+ " 							and f3.fi_form3_id not in (select fi_form3_id from adempiere.fi_form3exclude where isactive = 'Y' )  "				
//				+ " 						)	"
//				+ " 					) amt	"
//				
//				
//				);
//		return sql;
//	}
	
	private StringBuilder getSQl(Timestamp PstartDate, Timestamp PendDate, String fi_code) {
		StringBuilder sql = new StringBuilder();
		sql.append(
//				"select "
//				+ " sum(cp.payamt), f3.fi_code " 
//				+ " from c_payment cp "
//				+ " inner join fi_form3 f3 on f3.fi_form3_id = cp.fi_form3_id "
//				+ " where dateacct BETWEEN '" + PstartDate + "' and '"+ PendDate +"' "
//				+ " and fi_code = '" + fi_code + "' "
//				+ " group by f3.fi_code "
				" select(	\n"
				+ " 						(SELECT coalesce(abs(sum(l.stmtAmt * coalesce((select coalesce(r.multiplyrate,1) from adempiere.c_conversion_rate r where l.dateacct = r.validfrom  and l.c_currency_id = r.c_currency_id),1))),0) \n"
				+ " 							FROM adempiere.c_bankstatement h	\n"
				+ " 							inner join adempiere.c_bankstatementline l on l.c_bankstatement_id = h.c_bankstatement_id	\n"
				+ " 							inner join fi_form3 f3 on f3.fi_form3_id = l.fi_form3_id	\n"
				+ " 							where l.dateacct between '" + PstartDate + "' and '"+ PendDate +"' 	\n"
				+ " 							and f3.fi_code = '" + fi_code + "'	\n" 
//				+ " 							and f3.fi_form3_id not in (select fi_form3_id from adempiere.fi_form3exclude where isactive = 'Y' )  \n"
				+ "								and h.posted = 'Y'	\n"					
				+ " 						)	\n"
				+ " 						+ 	\n"
				+ " 						(select	\n" 
				+ " 							coalesce(abs(sum(cp.payamt * coalesce((select coalesce(r.multiplyrate,1) from adempiere.c_conversion_rate r where cp.dateacct = r.validfrom  and cp.c_currency_id = r.c_currency_id),1))),0)		\n"
				+ " 							from c_payment cp 		\n"
				+ " 							inner join fi_form3 f3 on f3.fi_form3_id = cp.fi_form3_id	\n"	 
				+ " 							where dateacct between '" + PstartDate + "' and '"+ PendDate +"' 		\n"
				+ " 							and f3.fi_code = '" + fi_code + "'	\n"
//				+ " 							and f3.fi_form3_id not in (select fi_form3_id from adempiere.fi_form3exclude where isactive = 'Y' )  \n"
				+ "								and cp.posted = 'Y'	\n"	
				+ " 						)	\n"
				+ " 						+	\n"
				+ " 						(	\n"
				+ " 						select coalesce(abs(sum(l.amtsourcedr * coalesce((select coalesce(r.multiplyrate,1) from adempiere.c_conversion_rate r where h.dateacct = r.validfrom  and l.c_currency_id = r.c_currency_id),1))),0)	\n"
				+ " 							from adempiere.gl_journal h	\n"
				+ " 							inner join adempiere.gl_journalline l on l.gl_journal_id = h.gl_journal_id	\n"
				+ " 							inner join fi_form3 f3 on f3.fi_form3_id = l.fi_form3_id 	\n"
				+ " 							where h.dateacct between '" + PstartDate + "' and '"+ PendDate +"'	\n"
				+ " 							and f3.fi_code = '" + fi_code + "'	\n"
//				+ " 							and f3.fi_form3_id not in (select fi_form3_id from adempiere.fi_form3exclude where isactive = 'Y' )  \n"
				+ "								and h.posted = 'Y'	\n"					
				+ " 						)	\n"
				+ " 					) amt	\n"
				);
		return sql;
	}
	
	private StringBuilder getSQlByAcct(Timestamp PstartDate,Timestamp PendDate, String acct) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				  " select coalesce(sum(fa.amtacctdr - fa.amtacctcr),0) from fact_acct fa, c_elementvalue ce " 
				+ " where fa.account_id=ce.c_elementvalue_id	"
				+ " and PostingType='A' "		  
				+ "and fa.dateacct < '"+PstartDate+"' and ce.value like '"+acct+"'"
				);				
//				Calendar cal = Calendar.getInstance();
//				cal.setTime(PstartDate);
//				cal.add(Calendar.MONTH, 1);
//				Timestamp tsStartDatePlusMonth = new Timestamp(cal.getTime().getTime());
//
//				sql.append(
//				"select(	\n" +
//				"		select case when (sum(fa.amtacctdr - fa.amtacctcr)) is null then 0 else (sum(fa.amtacctdr - fa.amtacctcr)) end	\n" + 
//				"		from fact_acct fa, c_elementvalue ce  \n"	+
//				"		where fa.account_id=ce.c_elementvalue_id	\n"	+	 
//				"		and fa.dateacct <= '" + tsStartDatePlusMonth + "' and ce.value like '"+acct+"')	\n"	+
//
//				"		+	\n"	+
//				"		(select case when (sum(fa.amtacctcr - fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr - fa.amtacctdr)) end	\n"	+ 
//				"		from fact_acct fa, c_elementvalue ce	\n"	+  
//				"		where fa.account_id=ce.c_elementvalue_id\n"	+	 
//				"		and fa.dateacct between '"+	PstartDate	+"' and '"+ PendDate +"' and ce.value like '"+acct+"'	\n"	+
//				"		)");					
		return sql;
	}
	
}